/***************************************************************************************
Firm-embedded productivity and cross-country income differences
Alviarez, Cravino and Ramondo
Journal of Political Economy (2022)

Program: table_B1_affiliates_parents_bysector.do
Date: October 2022

Description: Reproduces Table B.1: Number of affiliates and parents, by NAICS2.

*****************************************************************************************/

*-------------------------------------------------------------------------------
global typeden=1
include "set_directories.do"
set memory 64g
global lf "LF"

*Log
cap log close
log using "${clogs}/table_B1_affiliates_parents_bysector.log",replace
*-------------------------------------------------------------------------------



*Number of foreign affiliates by sector
*-------------------------------------------------------------------
use "${data}/sin_firmlevel_naics_gravityLF.dta", clear

keep if isocode=="DK" | isocode=="JP" | isocode=="IT" | isocode=="KR" | isocode=="DE" | isocode=="FR" | isocode=="ES" |  isocode=="MX" |  isocode=="GB" |  isocode=="PL" |  isocode=="GR" |  isocode=="NL" |  isocode=="RO" |  isocode=="BE" |  isocode=="AT" |  isocode=="PT" |  isocode=="FI" |  isocode=="SE" |  isocode=="CZ" |  isocode=="SK" |  isocode=="BG" |  isocode=="HU" |  isocode=="HR" |  isocode=="LV" |  isocode=="SI" |  isocode=="LT" |  isocode=="EE" 
keep if year==2016
drop if sin_sales==. | sin_sales==0

*keep MNCs only
by year sector guo_bvd isocode, sort: gen a=_n==1
replace a=0 if isocode==hq
by year sector guo_bvd, sort: egen b=total(a)
tab b 
keep if b>=2
drop a b 
tab isocode 
keep if isocode!=hq

tempfile temp
save `temp', replace 


use `temp', clear 
collapse (count) fa_sales=sin_sales fa_emp=sin_emp fa_va=sin_va, by(year sector1 sector)
drop if sector=="Public_Administration (O)"

replace sector1="1.Other goods" if sector1=="Other_Goods (A-B-D-E-F)"
replace sector1="2.Manufacturing" if sector1=="Manufacturing (C)"
replace sector1="3.Services" if sector1=="Market_Services (G-H-I-J-K-M-N-R-S-T)"
replace sector1="4.Other sectors" if sector1=="Non-Market Economy"

replace sector="1.Agriculture and Mining" if sector=="AgrMining (A-B)"
replace sector="2.Construction" if sector=="Construction (F)"
replace sector="3.Electricity" if sector=="Electricity (D-E)"

replace sector="4.Basic Metals" if sector=="Basic Metals (24-25)"
replace sector="3.Chemicals, Petroleum and Plastic" if sector=="ChePetPla (19-23)"
replace sector="5.Electrical Equipment and Machinery" if sector=="ElecMach (26-28)"
replace sector="1.Food and Beverages" if sector=="Food (10-12)"
replace sector="2.Textiles, Apparel and Wood" if sector=="TexWood (13-18)"
replace sector="6.Transport Equipment and Other Manufacturing" if sector=="TranspOtherManuf (29-33)"

replace sector="6.Accommodation and Recreation" if sector=="Accomod_Recreat (I-R-S)"
replace sector="4.Financial and Insurance Services" if sector=="Financial_Insurance (K)"
replace sector="3.Information" if sector=="Information (J)"
replace sector="5.Support Services" if sector=="Support_Services (M-N)"
replace sector="2.Transportation and Storage" if sector=="Transportation_Storage (H)"
replace sector="1.Wholesale Trade and Retail Trade" if sector=="Wholesale_Retail (G)"

replace sector="3.Education" if sector=="Education (P)"
replace sector="2.Health" if sector=="Health (Q)"
replace sector="1.Real Estate" if sector=="Real_Estate (L)"

sort sector1 sector
format %9.0fc fa*

tempfile temp_foreign_affiliates
save `temp_foreign_affiliates', replace



*Number of parents by sector
*-------------------------------------------------------------------
use "${data}/sin_firmlevel_naics_gravityLF.dta", clear

keep if isocode=="DK" | isocode=="JP" | isocode=="IT" | isocode=="KR" | isocode=="DE" | isocode=="FR" | isocode=="ES" |  isocode=="MX" |  isocode=="GB" |  isocode=="PL" |  isocode=="GR" |  isocode=="NL" |  isocode=="RO" |  isocode=="BE" |  isocode=="AT" |  isocode=="PT" |  isocode=="FI" |  isocode=="SE" |  isocode=="CZ" |  isocode=="SK" |  isocode=="BG" |  isocode=="HU" |  isocode=="HR" |  isocode=="LV" |  isocode=="SI" |  isocode=="LT" |  isocode=="EE" 
keep if year==2016
drop if sin_sales==. | sin_sales==0

*keep MNCs only
by year sector guo_bvd isocode, sort: gen a=_n==1
replace a=0 if isocode==hq
by year sector guo_bvd, sort: egen b=total(a)
tab b 
keep if b>=2
drop a b 
tab isocode 
keep if isocode==hq

collapse (count) pa_sales=sin_sales pa_emp=sin_emp pa_va=sin_va, by(year sector1 sector)
drop if sector=="Public_Administration (O)"

replace sector1="1.Other goods" if sector1=="Other_Goods (A-B-D-E-F)"
replace sector1="2.Manufacturing" if sector1=="Manufacturing (C)"
replace sector1="3.Services" if sector1=="Market_Services (G-H-I-J-K-M-N-R-S-T)"
replace sector1="4.Other sectors" if sector1=="Non-Market Economy"

replace sector="1.Agriculture and Mining" if sector=="AgrMining (A-B)"
replace sector="2.Construction" if sector=="Construction (F)"
replace sector="3.Electricity" if sector=="Electricity (D-E)"

replace sector="4.Basic Metals" if sector=="Basic Metals (24-25)"
replace sector="3.Chemicals, Petroleum and Plastic" if sector=="ChePetPla (19-23)"
replace sector="5.Electrical Equipment and Machinery" if sector=="ElecMach (26-28)"
replace sector="1.Food and Beverages" if sector=="Food (10-12)"
replace sector="2.Textiles, Apparel and Wood" if sector=="TexWood (13-18)"
replace sector="6.Transport Equipment and Other Manufacturing" if sector=="TranspOtherManuf (29-33)"

replace sector="6.Accommodation and Recreation" if sector=="Accomod_Recreat (I-R-S)"
replace sector="4.Financial and Insurance Services" if sector=="Financial_Insurance (K)"
replace sector="3.Information" if sector=="Information (J)"
replace sector="5.Support Services" if sector=="Support_Services (M-N)"
replace sector="2.Transportation and Storage" if sector=="Transportation_Storage (H)"
replace sector="1.Wholesale Trade and Retail Trade" if sector=="Wholesale_Retail (G)"

replace sector="3.Education" if sector=="Education (P)"
replace sector="2.Health" if sector=="Health (Q)"
replace sector="1.Real Estate" if sector=="Real_Estate (L)"

sort sector1 sector
format %9.0fc pa*

tempfile temp_parents
save `temp_parents', replace



*Merge foreing affiliates and parents by NAICS2 
*-------------------------------------------------
use `temp_foreign_affiliates', clear
merge 1:1 year sector using `temp_parents'
drop _merge

sort sector1 sector
format %9.0fc fa* pa*

export delimited using "${rappendix}/table_B1_affiliates_parents_bysector.csv", datafmt replace

log close 






